﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Settings;
using System.Data.SqlClient;
using System.Data;
using System.Data.OleDb;

namespace PanasonicWarranty.Business
{
    public partial class pa_Category : BaseClass
    {
        public static List<pa_Category> Paging(int currentPage, int pageSize, ref int totalRecord, string cateCode, string cateName, bool? disabled)
        {
            using (var context = new DBWarrantyDataContext(AppSetting.ConnectionString))
            {
                var resutl = context.pa_Categories.AsQueryable();
                if (!string.IsNullOrWhiteSpace(cateCode))
                {
                    resutl = resutl.Where(p => p.CateCode.Contains(cateCode));
                }
                if (!string.IsNullOrWhiteSpace(cateName))
                {
                    resutl = resutl.Where(p => p.CateName.Contains(cateName));
                }
                if (disabled != null)
                {
                    resutl = resutl.Where(p => p.Disabled == disabled);
                }
                totalRecord = resutl.Count();
                return resutl.Skip(((currentPage - 1) < 0 ? 0 : (currentPage - 1)) * pageSize).Take(pageSize).ToList();
            }
        }
        
        /// <summary>
        /// Insert và update dữ liệu vào bảng từ file
        /// </summary>
        /// <param name="filePath">tên file excel chứa dữ liệu</param>
        /// <returns></returns>
        public static int BulkInsert(string filePath)
        {
            int affected = 0;
            string tempTableName = "Temp" + Guid.NewGuid().ToString("N");
            DataTable dtSource = new DataTable();
            try
            {
                //Tạo bảng tạm chứa dữ liệu
                ExecuteQuery(string.Format("SELECT TOP(0) * INTO {0} FROM pa_Category", tempTableName));

                //Insert dữ liệu vào bảng tạm
                using (SqlBulkCopy bc = new SqlBulkCopy(AppSetting.ConnectionString))
                {
                    bc.BulkCopyTimeout = 0;
                    bc.DestinationTableName = tempTableName;
                    using (OleDbConnection Oleconn = GetOleDbConnection(filePath))
                    {
                        Oleconn.Open();
                        var dtSchema = Oleconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                        var sheet1 = dtSchema.Rows[0].Field<string>("TABLE_NAME");
                        using (OleDbCommand Olecmd = Oleconn.CreateCommand())
                        {
                            Olecmd.CommandText = string.Format(@"SELECT * FROM [{0}]", sheet1);
                            dtSource.Load(Olecmd.ExecuteReader());
                            TransformTable(dtSource);
                            foreach (DataColumn col in dtSource.Columns)
                                bc.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                            bc.WriteToServer(dtSource);
                        }
                    }
                }
                //Xử lý dữ liệu ở bảng tạm và xóa luôn bảng tạm
                affected = ExecuteQuery(string.Format(@"
                        UPDATE pa_Category
                        SET
                            CateCode = T.CateCode,
                            CateName = T.CateName,
                            [Description] = T.[Description],
                            [Disabled] = T.[Disabled]
                        FROM pa_Category pm INNER JOIN
                        (
	                        SELECT * FROM {0}
	                        EXCEPT 
	                        SELECT * FROM pa_Category pm
                        )T ON pm.CateCode = T.CateCode
                        INSERT INTO pa_Category
                        SELECT * FROM {0} T
                        WHERE T.CateCode NOT IN (SELECT CateCode FROM pa_Category pm)
                        DROP TABLE {0}
                        ", tempTableName));
                return affected;
            }
            catch (Exception ex)
            {
                //Xóa bảng tạm
                DeleteTempTable(tempTableName);
                throw ex;
            }
        }

        private static void TransformTable(DataTable dtSource)
        {
            while (dtSource.Columns.Count > 3)
                dtSource.Columns.RemoveAt(3);
            dtSource.Columns.Add("Disabled", typeof(bool));
            dtSource.Columns[0].ColumnName = "CateCode";
            dtSource.Columns[1].ColumnName = "CateName";
            dtSource.Columns[2].ColumnName = "Description";
            foreach (DataRow row in dtSource.Rows)
                row["Disabled"] = false;
        }
       
        public static bool CheckExists(DataTable dt)
        {
            SqlParameter param = new SqlParameter("@CategoryList", dt);
            param.SqlDbType = SqlDbType.Structured;
            param.TypeName = "dbo.Varchar50TableType";
            return (bool)ExecuteScalar("usp_pa_Category_CheckExists", CommandType.StoredProcedure, param);
        }

        public static DataTable GetAll(bool? disable)
        {
            string query = "SELECT CateCode, CateName, [Description], ISNULL(Disabled, 0) Disabled FROM pa_Category pc ";
            if (disable.HasValue)
                query += "WHERE ([Disabled] = " + (disable == true ? "1" : "0 OR [Disabled] IS NULL") + ")";
            return ExecuteReader(query, CommandType.Text);
        }

        public static string GetCaption(string columnName)
        {
            switch (columnName)
            {
                case "CateCode":
                    return "Mã nhóm sản phẩm";
                case "CateName":
                    return "Tên nhóm sản phẩm";
                case "Description":
                    return "Diễn giải";
                case "Disabled":
                    return "Hết hạn";
            }
            return "";
        }
    }
}
